package com.typingtutor.db;

import java.io.File;
import java.nio.file.Paths;
import java.sql.*;

public class DatabaseManager {
//    public static final String DB_URL = "jdbc:h2:./typing_tutor_db";
//    public static final String DB_URL = "jdbc:h2:" + System.getProperty("user.dir") + "/typing_tutor_db";
// 数据库配置
    public static final String DB_NAME = "typing_tutor_db";
    public static final String DB_USER = "sa";
    public static final String DB_PASSWORD = "";
    public static final String DB_URL;
    private static volatile DatabaseManager INSTANCE;

    private DatabaseManager() {
        // 私有构造函数，防止实例化
    }

    public static DatabaseManager getInstance() {
        if (INSTANCE == null) {
            synchronized (DatabaseManager.class) {
                if (INSTANCE == null) {
                    INSTANCE = new DatabaseManager();
                }
            }
        }
        return INSTANCE;
    }
    // 使用更安全的存储路径（跨平台）
    private static final String APP_DATA_DIR = Paths.get(
            System.getProperty("user.home"),
            ".typingtutor",
            "database"
    ).toString();

    static {
        // 确保数据库目录存在
        initDatabaseDir();

        // 构建安全的JDBC URL
        DB_URL = buildJdbcUrl();
    }

    private static void initDatabaseDir() {
        try {
            File dbDir = new File(APP_DATA_DIR);
            if (!dbDir.exists()) {
                if (dbDir.mkdirs()) {
                    System.out.println("Created database directory: " + APP_DATA_DIR);
                } else {
                    throw new RuntimeException("Failed to create database directory");
                }
            }

            // 设置目录权限（仅限类Unix系统）
            if (!System.getProperty("os.name").toLowerCase().contains("win")) {
                Runtime.getRuntime().exec("chmod 700 " + APP_DATA_DIR);
            }
        } catch (Exception e) {
            throw new RuntimeException("Database initialization failed", e);
        }
    }

    private static String buildJdbcUrl() {
        // 处理Windows路径中的空格和特殊字符
        String osSafePath = APP_DATA_DIR
                .replace("\\", "\\\\")  // 转义Windows反斜杠
                .replace(" ", "%20");   // URL编码空格

        return String.format(
                "jdbc:h2:%s/%s;DB_CLOSE_DELAY=-1;FILE_LOCK=FS",
                osSafePath,
                DB_NAME
        );
    }


    public static void createDatabase() {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement stmt = conn.createStatement()) {

            // 创建 folders 表
            String createFoldersTable = "CREATE TABLE IF NOT EXISTS folders (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    `name` VARCHAR(255) NOT NULL," +
                    "    parent_folder_id VARCHAR(36)," +
                    "    CONSTRAINT fk_folders_parent" +
                    "        FOREIGN KEY (parent_folder_id) REFERENCES folders(id)" +
                    ");";
            stmt.executeUpdate(createFoldersTable);
            // 为 parent_folder_id 创建索引
            createIndexIfNotExists(conn, "idx_folders_parent_folder_id", "folders", "parent_folder_id");

            // 创建 documents 表
            String createDocumentsTable = "CREATE TABLE IF NOT EXISTS documents (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    `name` VARCHAR(255) NOT NULL," +
                    "    folder_id VARCHAR(36)," +
                    "    CONSTRAINT fk_documents_folder" +
                    "        FOREIGN KEY (folder_id) REFERENCES folders(id)" +
                    ");";
            stmt.executeUpdate(createDocumentsTable);
            // 为 folder_id 创建索引
            createIndexIfNotExists(conn, "idx_documents_folder_id", "documents", "folder_id");

            // 创建 field_key_info 表
            String createFieldKeyInfoTable = "CREATE TABLE IF NOT EXISTS field_key_info (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    field_key VARCHAR(255) NOT NULL," +
                    "    `display` BOOLEAN DEFAULT FALSE" +
                    ");";
            stmt.executeUpdate(createFieldKeyInfoTable);
            // 为 field_key_info 表的主键创建索引（实际上主键默认会有索引，这里显式创建确保一致性）
            createIndexIfNotExists(conn, "idx_field_key_info_id", "field_key_info", "id");

            // 创建 folder_random_rules 表
            String createRandomRulesTable = "CREATE TABLE IF NOT EXISTS folder_random_rules (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    random_range VARCHAR(255) NOT NULL," +
                    "    operator_str VARCHAR(255)," +
                    "    math_field VARCHAR(255)," +
                    "    default_enabled BOOLEAN," +
                    "    folder_id VARCHAR(36)," +
                    "    sort_order INT AUTO_INCREMENT," +
                    "    CONSTRAINT fk_folder_random_rules_folder" +
                    "        FOREIGN KEY (folder_id) REFERENCES folders(id)" +
                    ");";
            stmt.executeUpdate(createRandomRulesTable);
            // 为 folder_random_rules 表的主键创建索引（实际上主键默认会有索引，这里显式创建确保一致性）
            createIndexIfNotExists(conn, "idx_folder_random_rules_id", "folder_random_rules", "id");
            // 为 folder_random_rules 表的外键 folder_id 创建索引
            createIndexIfNotExists(conn, "idx_folder_random_rules_folder_id", "folder_random_rules", "folder_id");

            // 创建中间关联表 folder_rule_field_key_relation
            String createRelationTable = "CREATE TABLE IF NOT EXISTS folder_rule_field_key_relation (" +
                    "    folder_random_rules_id VARCHAR(36)," +
                    "    field_key_info_id VARCHAR(36)," +
                    "    PRIMARY KEY (folder_random_rules_id, field_key_info_id)," +
                    "    CONSTRAINT fk_relation_folder_random_rules" +
                    "        FOREIGN KEY (folder_random_rules_id) REFERENCES folder_random_rules(id) ON DELETE CASCADE," +
                    "    CONSTRAINT fk_relation_field_key_info" +
                    "        FOREIGN KEY (field_key_info_id) REFERENCES field_key_info(id) ON DELETE CASCADE" +
                    ");";
            stmt.executeUpdate(createRelationTable);
            // 为 folder_random_rules_id 创建索引
            createIndexIfNotExists(conn, "idx_folder_rule_field_key_relation_folder_random_rules_id", "folder_rule_field_key_relation", "folder_random_rules_id");
            // 为 field_key_info_id 创建索引
            createIndexIfNotExists(conn, "idx_folder_rule_field_key_relation_field_key_info_id", "folder_rule_field_key_relation", "field_key_info_id");

            // 创建 field_mapping 表
            String createFieldMappingTable = "CREATE TABLE IF NOT EXISTS field_mapping (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    field_value VARCHAR(255)," +
                    "    table_create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                    "    document_id VARCHAR(36)," +
                    "    sort_order INT AUTO_INCREMENT," +
                    "    CONSTRAINT fk_field_mapping_document" +
                    "        FOREIGN KEY (document_id) REFERENCES documents(id)" +
                    ");";
            stmt.executeUpdate(createFieldMappingTable);
            // 为 document_id 创建索引
            createIndexIfNotExists(conn, "idx_field_mapping_document_id", "field_mapping", "document_id");

            // 创建 field_key_mapping_relation 表
            String createFieldKeyMappingRelationTable = "CREATE TABLE IF NOT EXISTS field_key_mapping_relation (" +
                    "    field_key_id VARCHAR(36)," +
                    "    field_mapping_id VARCHAR(36)," +
                    "    PRIMARY KEY (field_key_id, field_mapping_id)," +
                    "    CONSTRAINT fk_relation_field_key" +
                    "        FOREIGN KEY (field_key_id) REFERENCES field_key_info(id) ON DELETE CASCADE," +
                    "    CONSTRAINT fk_relation_field_mapping" +
                    "        FOREIGN KEY (field_mapping_id) REFERENCES field_mapping(id) ON DELETE CASCADE" +
                    ");";
            stmt.executeUpdate(createFieldKeyMappingRelationTable);
            // 为 field_key_id 创建索引
            createIndexIfNotExists(conn, "idx_field_key_mapping_relation_field_key_id", "field_key_mapping_relation", "field_key_id");
            // 为 field_mapping_id 创建索引
            createIndexIfNotExists(conn, "idx_field_key_mapping_relation_field_mapping_id", "field_key_mapping_relation", "field_mapping_id");

            // 创建 typing_data 表，去除外键约束
            String createTypingDataTable = "CREATE TABLE IF NOT EXISTS typing_data (" +
                    "    id VARCHAR(36) PRIMARY KEY," +
                    "    `date` VARCHAR(10) NOT NULL," +
                    "    display_name VARCHAR(255) NOT NULL," +
                    "    correct_count INT NOT NULL," +
                    "    error_count INT NOT NULL," +
                    "    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP " +
                    ")";
            stmt.executeUpdate(createTypingDataTable);
            // 为 display_name 创建索引
            createIndexIfNotExists(conn, "idx_typing_data_display_name", "typing_data", "display_name");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void createIndexIfNotExists(Connection conn, String indexName, String tableName, String columnName) throws SQLException {
        String checkIndexSql = "SELECT * FROM INFORMATION_SCHEMA.INDEXES WHERE UPPER(INDEX_NAME) = UPPER(?) AND UPPER(TABLE_NAME) = UPPER(?)";
        try (PreparedStatement pstmt = conn.prepareStatement(checkIndexSql)) {
            pstmt.setString(1, indexName);
            pstmt.setString(2, tableName);
            try (ResultSet rs = pstmt.executeQuery()) {
                if (!rs.next()) {
                    // 索引不存在，创建索引
                    String createIndexSql = "CREATE INDEX " + indexName + " ON " + tableName + " (" + columnName + ")";
                    try (Statement createStmt = conn.createStatement()) {
                        createStmt.executeUpdate(createIndexSql);
                    }
                }
            }
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
    }
}